﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Reports.PurchaseBook
{
    public class DataLoader
    {
        public static void GetData(Reports.PurchaseBook.DataSet.DataDataTable data, string ConnectionString, string depName, DateTime dateF, DateTime dateT)
        {
            string QueryString =
    @"SELECT @DepartName as DepartName, CA.Documents.ContragentDocNumber AS SupplierDocNum, CA.Documents.ContragentDocDate AS SupplierDocDate, 
CA.Documents.PaymentDate AS PayDate, CA.Documents.DocDate, CA.Contragents.Name AS SupplierName, CA.Contragents.INN AS SupplierInn, 
CA.Contragents.KPP AS SupplierKpp,'' as CountryName, '' as GTDNumber,
(SELECT     SUM(CD.InputLines.SupplierPrice * CD.InputLines.DocQntty)
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id)) as Sum,
(SELECT     SUM(SupplierPrice * DocQntty)
FROM         CD.InputLines
WHERE     (DocId = CA.Documents.Id) AND (NDSTax IS NULL)) AS SumNull,
(SELECT     SUM(SupplierPrice * DocQntty)
FROM         CD.InputLines
WHERE     (DocId = CA.Documents.Id) AND (NDSTax = 0)) AS Sum0,
(SELECT     SUM(SupplierPrice * DocQntty * 100 / 110)
FROM         CD.InputLines
WHERE     (DocId = CA.Documents.Id) AND (NDSTax = 10)) AS Sum10,
(SELECT     SUM(SupplierPrice * DocQntty * 10 / 110) 
FROM         CD.InputLines
WHERE     (DocId = CA.Documents.Id) AND (NDSTax = 10)) AS VatSum10,
(SELECT     SUM(SupplierPrice * DocQntty * 100 / 118)
FROM         CD.InputLines
WHERE     (DocId = CA.Documents.Id) AND (NDSTax = 18)) AS Sum18,
(SELECT     SUM(SupplierPrice * DocQntty * 18 / 118) 
FROM         CD.InputLines
WHERE     (DocId = CA.Documents.Id) AND (NDSTax = 18)) AS VatSum18
FROM CA.Documents INNER JOIN
CA.Contragents ON CA.Documents.ContragentId = CA.Contragents.Id
WHERE(CA.Documents.DocTypeId = 2) AND CA.Documents.DocDate BETWEEN @DateF AND @DateT";
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter DateF = new SqlParameter("@DateF", SqlDbType.DateTime);
                    DateF.Value = dateF;
                    command.Parameters.Add(DateF);
                    SqlParameter DateT = new SqlParameter("@DateT", SqlDbType.DateTime);
                    DateT.Value = dateT;
                    command.Parameters.Add(DateT);
                    SqlParameter DepartName = new SqlParameter("@DepartName", SqlDbType.VarChar, 25);
                    DepartName.Value = depName;
                    command.Parameters.Add(DepartName);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
        }
        public static void GetInfo(Reports.PurchaseBook.DataSet.InfoDataTable info, string ConnectionString, DateTime dateF, DateTime dateT)
        {
            string QueryString = 
@"SELECT     Name , INN, KPP
FROM         CA.Contragents
WHERE     (Id = 0)";
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        Int32 inn,name,kpp;
                        Reports.PurchaseBook.DataSet.InfoRow ir = info.NewInfoRow();
                        ir.AptekaInn = string.Empty;
                        ir.AptekaKpp = string.Empty;
                        ir.AptekaName = string.Empty;
                        ir.DateF = dateF;
                        ir.DateT = dateT;
                        inn = reader.GetOrdinal("INN");
                        kpp  = reader.GetOrdinal("KPP");
                        name = reader.GetOrdinal("Name");
                        if (!reader.IsDBNull(name))
                        {
                            ir.AptekaName = (string)reader[name];
                        }
                        if (!reader.IsDBNull(kpp))
                        {
                            ir.AptekaKpp = (string)reader[kpp];
                        }
                        if (!reader.IsDBNull(inn))
                        {
                            ir.AptekaInn = (string)reader[inn];
                        }
                        info.Rows.Add(ir);

                    }
                    else
                    {
                        throw new Exception("Невозможно прочитать данные");
                    }
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
        }
    }
}
